const Database=require("better-sqlite3");
const path=require("path");
const db=new Database(path.join(__dirname,'./demoData.sqlite'),{
    fileMustExist:true,
    verbose:console.log
});

const loginFun=function(user={}){
    let sql='select id,username from t_user where username=? and password=?';
    let stmt=db.prepare(sql);
    let rows=stmt.get(user.username,user.password);
    return rows;//单个对象
};
const loadClassList=function(){
    let sql='select id,name from t_class';
    let stmt=db.prepare(sql);
    let rows=stmt.all();
    return rows;//数组
};
const loadStuList=function(){
    let sql='select s.id,s.name,s.link,s.img,s.cid,c.name className from t_student s,t_class c where s.cid=c.id;';
    let stmt=db.prepare(sql);
    let rows=stmt.all();
    return rows;
};
const insertClass=function(className){
    let sql='insert into t_class (name) values (?)';
    let stmt=db.prepare(sql);
    let rows=stmt.run(className);
    return rows;
};
const findClassById=function(id=''){
    let sql='select id,name from t_class where id=?';
    let stmt=db.prepare(sql);
    let row=stmt.get(id);
    return row;
};

const updateClassById=function(classObj={}){
    let sql='update t_class set name=? where id=?';
    let stmt=db.prepare(sql);
    let row=stmt.run(classObj.className,classObj.id);
    return row;
};
const deleteClassById=function(id=''){
    let sql='delete from t_class where id=?';
    let stmt=db.prepare(sql);
    let row=stmt.run(id);
    return row;
};
const addStu=function(stu={}){
    let sql='insert into t_student (name,link,img,cid) values (?,?,?,?)';
    let stmt=db.prepare(sql);
    let row=stmt.run(stu.name,stu.link,stu.img,stu.cid);
    return row;
};
const findStuById=function(id=''){
    let sql='select id,name,link,img,cid from t_student where id=?';
    let stmt=db.prepare(sql);
    let row=stmt.get(id);
    return row;
};
const updateStuById=function(stu={}){
    let sql='update t_student set name=?,link=?,img=?,cid=? where id=?';
    let stmt=db.prepare(sql);
    let row=stmt.run(stu.name,stu.link,stu.img,stu.cid,stu.id);
    return row;
};
const deleteStuById=function(id=''){
    let sql='delete from t_student  where id=?';
    let stmt=db.prepare(sql);
    let row=stmt.run(id);
    return row;
};
module.exports={
    loginFun,
    loadClassList,
    loadStuList,
    insertClass,
    findClassById,
    updateClassById,
    deleteClassById,
    addStu,
    findStuById,
    updateStuById,
    deleteStuById
};